

/**
	AUTHOR		: GANDHI
	DATE		: 12 JULY, 2005
	DESCRIPTION	: THIS PROC IS USED TO GET SALES FORECAST DETAILS BY QUARTER BASED ON SEARCH CRITERIA. 


	
	EXEC SP_NEW_SALES_FORECAST_REPORT  '0','0','0',3,2005,'0','0',0.0,'byQuarter',6,11,42

*/


ALTER    PROC SP_NEW_SALES_FORECAST_REPORT(
 
 
 @ODM_INPUT VARCHAR(100), 
 @PRODUCT_INPUT VARCHAR(100),
 @SPERSON_INPUT VARCHAR(100),
 @QUARTER INT,		--1 OR 2 OR 3 OR 4 	
 @YEAR INT, 
 @TYPE VARCHAR(20),   --0
 @defaultPrompt varchar(20),
 @CONF_LEVEL FLOAT,
 @PERIOD VARCHAR(20),	--byQuarter
 @FROM_MONTH INT,	--starts from Zero(0 to 11)
 @TO_MONTH INT, 	--starts from Zero(0 to 11)
 @CUSTOMER_INPUT VARCHAR(100) )
 
  
 AS
 BEGIN
	
	
	DECLARE @RESULTS TABLE( BUILDNO VARCHAR(50), SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, QUANTITY1 INT,QUANTITY2 INT,QUANTITY3 INT, PRICE1 FLOAT ,PRICE2 FLOAT ,PRICE3 FLOAT , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL1 FLOAT,CONFIDENCE_LEVEL2 FLOAT,CONFIDENCE_LEVEL3 FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50), SPERSON_NAME VARCHAR(100), QUARTER_START INT, QUARTER_END INT)
	DECLARE @SPERSON_RESULTS TABLE( BUILDNO VARCHAR(50), SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, QUANTITY1 INT,QUANTITY2 INT,QUANTITY3 INT, PRICE1 FLOAT ,PRICE2 FLOAT ,PRICE3 FLOAT , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL1 FLOAT,CONFIDENCE_LEVEL2 FLOAT,CONFIDENCE_LEVEL3 FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50), SPERSON_NAME VARCHAR(100), QUARTER_START INT, QUARTER_END INT )
   	DECLARE @TOTAL_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, QUANTITY1 INT,QUANTITY2 INT,QUANTITY3 INT, PRICE1 FLOAT ,PRICE2 FLOAT ,PRICE3 FLOAT , CONFIDENCE_LEVEL1 FLOAT,CONFIDENCE_LEVEL2 FLOAT,CONFIDENCE_LEVEL3 FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50), SPERSON_NAME VARCHAR(100), QUARTER_START INT, QUARTER_END INT )	
	
	
	DECLARE @START_MONTH INT 
	DECLARE @END_MONTH INT

	IF @PERIOD IS NOT NULL AND LTRIM(RTRIM(@PERIOD)) = 'byQuarter'
	BEGIN
		IF @QUARTER = 1
		BEGIN
			SET @START_MONTH = 0
			SET @END_MONTH = 2
		END
		ELSE IF @QUARTER = 2
		BEGIN
			SET @START_MONTH = 3
			SET @END_MONTH = 5
		END
		ELSE IF @QUARTER = 3
		BEGIN
			SET @START_MONTH = 6
			SET @END_MONTH = 8
		END
		ELSE IF @QUARTER = 4
		BEGIN
			SET @START_MONTH = 9
			SET @END_MONTH = 11
		END
		ELSE
		BEGIN
			SET @START_MONTH = 0
			SET @END_MONTH = 11
		END
	END
	/*ELSE IF @PERIOD IS NOT NULL AND LTRIM(RTRIM(@PERIOD)) = 'byMonth'
	BEGIN
		SET @START_MONTH   = 	@FROM_MONTH
		SET @END_MONTH 	   = 	@TO_MONTH
	END
	*/

	DECLARE @SF_SPID INT
	DECLARE @SF_SPERSON_NAME VARCHAR(100)
	DECLARE @SF_CUSTOMER VARCHAR(100)
 	DECLARE @SF_PRODUCT VARCHAR(50)
 	DECLARE @SF_PROJECT VARCHAR(50)
 	DECLARE @SF_ODM VARCHAR(100)
	DECLARE @SF_CUSTOMER_NAME VARCHAR(100)
 	DECLARE @SF_PRODUCT_NAME VARCHAR(50)
 	DECLARE @SF_PROJECT_NAME VARCHAR(50)
 	DECLARE @SF_ODM_NAME VARCHAR(100)
 	DECLARE @SF_MONTH INT
	DECLARE @SF_QUARTER INT
 	DECLARE @SF_YEAR INT
 	DECLARE @SF_QUANTITY INT
	DECLARE @SF_PRICE FLOAT
 	DECLARE @SF_USERLIST1 VARCHAR(100)
	DECLARE @SF_USERDATE1 VARCHAR(100)
	DECLARE @SF_USERTXT1 VARCHAR(100)
	DECLARE @SF_USERTXT2 VARCHAR(100)
	DECLARE @SF_CONFIDENCE_LEVEL FLOAT
	DECLARE @MKTG_PART_NUMBER VARCHAR(100)
	DECLARE @SPERSON_SPID INT
	DECLARE @LEVEL_ORDER INT
	DECLARE @FC_MONTH VARCHAR(50)	
	DECLARE @FC_QUARTER VARCHAR(50)
	DECLARE @SF_FORECAST_TYPE VARCHAR(50)
	DECLARE @SF_BUILDNO VARCHAR(50)
	DECLARE @QUARTER_START INT
	DECLARE @QUARTER_END INT
	DECLARE @QUANTITY1 INT
	DECLARE @QUANTITY2 INT
	DECLARE @QUANTITY3 INT
	DECLARE @PRICE1 FLOAT
	DECLARE @PRICE2 FLOAT
	DECLARE @PRICE3 FLOAT
	DECLARE @CONFIDENCE_LEVEL1 FLOAT
	DECLARE @CONFIDENCE_LEVEL2 FLOAT
	DECLARE @CONFIDENCE_LEVEL3 FLOAT

 	DECLARE @SPERSON_CURSOR CURSOR 
	
	--DECLARE @CUSTOMER_INPUT VARCHAR(100)
	DECLARE @PROJECT_INPUT VARCHAR(100)
	--SET @CUSTOMER_INPUT = NULL
	SET @PROJECT_INPUT = NULL

	IF @CUSTOMER_INPUT IS NULL  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = ''  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = '-1'  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = '-2' 
	BEGIN	
		SET @CUSTOMER_INPUT = '0'
	END
	IF @PROJECT_INPUT IS NULL  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = ''  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = '-1'  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = '-2'
	BEGIN	
		SET @PROJECT_INPUT = '0'
	END
	IF @PRODUCT_INPUT IS NULL  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = ''  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = '-1'  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = '-2'
	BEGIN
		SET @PRODUCT_INPUT = '0'
	END
	IF @ODM_INPUT IS NULL  OR  LTRIM(RTRIM(@ODM_INPUT)) = ''  OR  LTRIM(RTRIM(@ODM_INPUT)) = '-1'  OR  LTRIM(RTRIM(@ODM_INPUT)) = '-2'
	BEGIN
		SET @ODM_INPUT = '0'
	END	
	IF @SPERSON_INPUT IS NULL  OR  LTRIM(RTRIM(@SPERSON_INPUT)) = ''  OR  LTRIM(RTRIM(@SPERSON_INPUT)) = '-1'  OR  LTRIM(RTRIM(@SPERSON_INPUT)) = '-2'
	BEGIN
		SET @SPERSON_INPUT = '0'
	END	

	DECLARE @REPORT_ON VARCHAR(10)
	SET @REPORT_ON = 'SALES'
	 	
 	DECLARE @SQLSTRING NVARCHAR(3000)
	DECLARE @PARAMDEFINITION NVARCHAR(500)

	IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
	BEGIN
		
		SET @SQLSTRING = N'DECLARE FORECASTCURSOR CURSOR FOR SELECT NULL, SPERSONID, CUSTID, PROJID, PRODID, ODMID, MONTH, QUARTER, YEAR, QTY, QTY*PRICE, USERLIST1, USERDATE1, USERTXT1, USERTXT2, CONFIDENCE_LEVEL, ''SALES'' FROM FCENTRYMASTER WHERE ROWID IS NOT NULL '
		SET @PARAMDEFINITION = N'@PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @PRM_SPERSON_INPUT VARCHAR(100), @START_MONTH INT, @END_MONTH INT, @YEAR INT, @PRM_CONF_LEVEL FLOAT, @PRM_CUSTOMER_INPUT VARCHAR(100)'
		
		IF @PRODUCT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PRODID = @PRM_PRODUCT_INPUT '
		END
		IF @ODM_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ODMID = @PRM_ODM_INPUT '
		END
		IF @SPERSON_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND SPERSONID = @PRM_SPERSON_INPUT '
		END	
		IF @CONF_LEVEL <> -1.0
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND CONFIDENCE_LEVEL >= @PRM_CONF_LEVEL '
		END
		IF @CUSTOMER_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND CUSTID = @PRM_CUSTOMER_INPUT '
		END	
		
		SET @SQLSTRING = @SQLSTRING + ' AND ( MONTH >= @START_MONTH AND YEAR = @YEAR AND MONTH <= @END_MONTH ) '	

		EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_PRODUCT_INPUT = @PRODUCT_INPUT, @PRM_ODM_INPUT = @ODM_INPUT,  @PRM_SPERSON_INPUT = @SPERSON_INPUT, @START_MONTH = @START_MONTH, @END_MONTH = @END_MONTH, @YEAR = @YEAR, @PRM_CONF_LEVEL = @CONF_LEVEL, @PRM_CUSTOMER_INPUT = @CUSTOMER_INPUT
	END

	
	SET @SQLSTRING = NULL
	SET @PARAMDEFINITION = NULL
	
	IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
	BEGIN
		
		SET @SQLSTRING = N'DECLARE SPERSON_CURSOR CURSOR FOR SELECT DISTINCT FCSPERSONREL.SPERSONID, LEVELORDER FROM FCSPERSONREL WHERE 
				   FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE ROWID IS NOT NULL'
		SET @PARAMDEFINITION = N'@PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @PRM_SPERSON_INPUT VARCHAR(100), @PRM_CUSTOMER_INPUT VARCHAR(100)'
		
		IF @PRODUCT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PRODID = @PRM_PRODUCT_INPUT '
		END
		IF @ODM_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ODMID = @PRM_ODM_INPUT '
		END
		IF @SPERSON_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND SPERSONID = @PRM_SPERSON_INPUT '
		END
		IF @CUSTOMER_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND CUSTID = @PRM_CUSTOMER_INPUT '
		END		
				
		SET @SQLSTRING = @SQLSTRING + ' ) ORDER BY LEVELORDER DESC, SPERSONID '	

		EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_PRODUCT_INPUT = @PRODUCT_INPUT, @PRM_ODM_INPUT = @ODM_INPUT, @PRM_SPERSON_INPUT = @SPERSON_INPUT, @PRM_CUSTOMER_INPUT = @CUSTOMER_INPUT
	END
	

/* 	IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT <> '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN 
			SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN  
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID 
		END
	END
 	
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT = '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END
 	
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT = '0'
 	BEGIN
	 	IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
			SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PROJID = @PROJECT_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END	
 
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE ODMID = @ODM_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END
 	
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END
 	
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PROJID = @PROJECT_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END
 	
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT <> '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END
	
	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT <> '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END
 	
	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT = '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT )
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END

 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER)
			AND LEVELORDER NOT IN ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 ) ORDER BY LEVELORDER DESC, SPERSONID
		END
	END
 */
	

	DECLARE @PART_NUMBER_TYPE INT
	SELECT @PART_NUMBER_TYPE = PART_NUMBER_TYPE FROM SYSTEMPRFS

		OPEN FORECASTCURSOR 
 			FETCH NEXT FROM FORECASTCURSOR
 			INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE
 			WHILE @@FETCH_STATUS = 0
 			BEGIN

				SET @SF_CUSTOMER_NAME = NULL
				SET @SF_PROJECT_NAME = NULL
				SET @SF_PRODUCT_NAME = NULL
				SET @SF_ODM_NAME = NULL
				SET @SF_SPERSON_NAME = NULL
				SELECT @SF_CUSTOMER_NAME = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @SF_CUSTOMER
				SELECT @SF_PROJECT_NAME = PROJNAME FROM  PROJSETUP WHERE PROJID = @SF_PROJECT
				SELECT @SF_PRODUCT_NAME = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @SF_PRODUCT
				SELECT @SF_ODM_NAME = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @SF_ODM
				SELECT @SF_SPERSON_NAME = SALESPERSON_NAME FROM SALES_PERSONS WHERE SALESPERSON_ID = @SF_SPID
				
				IF ( @SF_CONFIDENCE_LEVEL < 20 )
				BEGIN
					SET @SF_CONFIDENCE_LEVEL = 0.0;
				END
				ELSE IF ( @SF_CONFIDENCE_LEVEL < 45 )
				BEGIN
					SET @SF_CONFIDENCE_LEVEL = 25.0;
				END
				ELSE IF ( @SF_CONFIDENCE_LEVEL < 70 )
				BEGIN
					SET @SF_CONFIDENCE_LEVEL = 50.0;
				END
				ELSE IF ( @SF_CONFIDENCE_LEVEL < 85 )
				BEGIN
					SET @SF_CONFIDENCE_LEVEL = 75.0;
				END
				ELSE IF ( @SF_CONFIDENCE_LEVEL < 95 )
				BEGIN
					SET @SF_CONFIDENCE_LEVEL = 90.0;
				END
				ELSE
				BEGIN
					SET @SF_CONFIDENCE_LEVEL = 100.0;
				END

				IF (@SF_MONTH >=0 AND @SF_MONTH <=2)
	  			BEGIN
	  				SET @QUARTER_START = 1
	  				SET @QUARTER_END =3
					IF (@SF_MONTH = 0)
					BEGIN
						SET @QUANTITY1 = @SF_QUANTITY
						SET @PRICE1 = @SF_PRICE
						SET @CONFIDENCE_LEVEL1 = @SF_CONFIDENCE_LEVEL
					END
					ELSE IF (@SF_MONTH = 1)
					BEGIN
						SET @QUANTITY2 = @SF_QUANTITY
						SET @PRICE2 = @SF_PRICE
						SET @CONFIDENCE_LEVEL2 = @SF_CONFIDENCE_LEVEL
					END
					ELSE IF (@SF_MONTH = 2)
					BEGIN
						SET @QUANTITY3 = @SF_QUANTITY
						SET @PRICE3 = @SF_PRICE
						SET @CONFIDENCE_LEVEL3 = @SF_CONFIDENCE_LEVEL
					END
		
	  			END
	  			ELSE IF (@SF_MONTH >=3 AND @SF_MONTH <=5)
	  			BEGIN
	  				SET @QUARTER_START = 2
	  				SET @QUARTER_END =6
					IF (@SF_MONTH = 3)
					BEGIN
						SET @QUANTITY1 = @SF_QUANTITY
						SET @PRICE1 = @SF_PRICE
						SET @CONFIDENCE_LEVEL1 = @SF_CONFIDENCE_LEVEL
					END
					ELSE IF (@SF_MONTH = 4)
					BEGIN
						SET @QUANTITY2 = @SF_QUANTITY
						SET @PRICE2 = @SF_PRICE
						SET @CONFIDENCE_LEVEL2 = @SF_CONFIDENCE_LEVEL
					END
					ELSE IF (@SF_MONTH = 5)
					BEGIN
						SET @QUANTITY3 = @SF_QUANTITY
						SET @PRICE3 = @SF_PRICE
						SET @CONFIDENCE_LEVEL3 = @SF_CONFIDENCE_LEVEL
					END
	 			END
	  			ELSE IF (@SF_MONTH >=6 AND @SF_MONTH <=8)
	  			BEGIN
	  				SET @QUARTER_START = 3
	  				SET @QUARTER_END =9
					IF (@SF_MONTH = 6)
					BEGIN
						SET @QUANTITY1 = @SF_QUANTITY
						SET @PRICE1 = @SF_PRICE
						SET @CONFIDENCE_LEVEL1 = @SF_CONFIDENCE_LEVEL
					END
					ELSE IF (@SF_MONTH = 7)
					BEGIN
						SET @QUANTITY2 = @SF_QUANTITY
						SET @PRICE2 = @SF_PRICE
						SET @CONFIDENCE_LEVEL2 = @SF_CONFIDENCE_LEVEL
					END
					ELSE IF (@SF_MONTH = 8)
					BEGIN
						SET @QUANTITY3 = @SF_QUANTITY
						SET @PRICE3 = @SF_PRICE
						SET @CONFIDENCE_LEVEL3 = @SF_CONFIDENCE_LEVEL
					END
	  			END						
	  			ELSE IF (@SF_MONTH >=9 AND @SF_MONTH <=11)
	  			BEGIN
	  				SET @QUARTER_START = 4
	  				SET @QUARTER_END =12
					IF (@SF_MONTH = 9)
					BEGIN
						SET @QUANTITY1 = @SF_QUANTITY
						SET @PRICE1 = @SF_PRICE
						SET @CONFIDENCE_LEVEL1 = @SF_CONFIDENCE_LEVEL
					END
					ELSE IF (@SF_MONTH = 10)
					BEGIN
						SET @QUANTITY2 = @SF_QUANTITY
						SET @PRICE2 = @SF_PRICE
						SET @CONFIDENCE_LEVEL2 = @SF_CONFIDENCE_LEVEL
					END
					ELSE IF (@SF_MONTH = 11)
					BEGIN
						SET @QUANTITY3 = @SF_QUANTITY
						SET @PRICE3 = @SF_PRICE
						SET @CONFIDENCE_LEVEL3 = @SF_CONFIDENCE_LEVEL
					END
	   			END
 				
				INSERT INTO @RESULTS VALUES( @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER_NAME, @SF_PROJECT_NAME, @SF_PRODUCT_NAME, @SF_ODM_NAME, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @QUANTITY1,@QUANTITY2,@QUANTITY3,@PRICE1 ,@PRICE2 ,@PRICE3 , /*@SF_PRICE,*/ @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2,@CONFIDENCE_LEVEL1,@CONFIDENCE_LEVEL2,@CONFIDENCE_LEVEL3,/*@SF_CONFIDENCE_LEVEL,*/ @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_FORECAST_TYPE, @SF_SPERSON_NAME, @QUARTER_START, @QUARTER_END )
 				
				IF @PART_NUMBER_TYPE <> 1
				BEGIN	
					SELECT @MKTG_PART_NUMBER = PRODUCT.P_MARKETING_PART_NUMBER FROM PRODUCT,PROCESS_STEP  WHERE PRODUCT.PRODUCT_ID = @SF_PRODUCT AND PRODUCT.PROCESSSTEP_ID IN (SELECT PROCESSSTEP_ID FROM PROCESS_STEP WHERE PS_TYPE_NAME LIKE 'FINISHED GOODS') AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID  
					UPDATE @RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE PRODUCT = @SF_PRODUCT_NAME
 				END

				SET @SF_MONTH = NULL
				SET @QUANTITY1 = NULL
				SET @QUANTITY2 = NULL
				SET @QUANTITY3 = NULL
				SET @PRICE1 = NULL
				SET @PRICE2 = NULL
				SET @PRICE3 = NULL
				SET @CONFIDENCE_LEVEL1 = NULL
				SET @CONFIDENCE_LEVEL2 = NULL
				SET @CONFIDENCE_LEVEL3 = NULL
				SET @MKTG_PART_NUMBER = NULL	

 				FETCH NEXT FROM FORECASTCURSOR
 				INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE				
 			END	

 		CLOSE FORECASTCURSOR
 		DEALLOCATE FORECASTCURSOR 
		
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
			OPEN SPERSON_CURSOR 
	 			FETCH NEXT FROM SPERSON_CURSOR
	 			INTO @SPERSON_SPID, @LEVEL_ORDER
	 			WHILE @@FETCH_STATUS = 0
	 			BEGIN
	
					INSERT INTO @SPERSON_RESULTS SELECT * FROM @RESULTS WHERE SPERSON_ID = @SPERSON_SPID
					 										
	 				FETCH NEXT FROM SPERSON_CURSOR
	 				INTO @SPERSON_SPID, @LEVEL_ORDER
	 			END	
	 				--DELETE FROM @RESULTS
					--INSERT INTO @RESULTS SELECT * FROM @SPERSON_RESULTS
					INSERT INTO @TOTAL_RESULTS SELECT  SPERSON_ID, CUSTOMER, PROJECT, PRODUCT, ODM, /*MONTH,*/NULL, QUARTER, YEAR, SUM(QUANTITY1) ,SUM(QUANTITY2),SUM(QUANTITY3), SUM(PRICE1) ,SUM(PRICE2) ,SUM(PRICE3) ,/*SUM(PRICE) PRICE,*/ /*AVG(CONFIDENCE_LEVEL) CONFIDENCE_LEVEL,*/AVG(CONFIDENCE_LEVEL1),AVG(CONFIDENCE_LEVEL2),AVG(CONFIDENCE_LEVEL3), CUSTID, PROJID, PRODID, ODMID, FORECAST_TYPE, SPERSON_NAME, QUARTER_START, QUARTER_END FROM @SPERSON_RESULTS				
					GROUP BY SPERSON_ID, CUSTOMER, CUSTID, PROJECT, PROJID, PRODUCT, PRODID, ODM, ODMID, FORECAST_TYPE, SPERSON_NAME, /*MONTH,*/ QUARTER, QUARTER_START, QUARTER_END, YEAR
					DELETE FROM @RESULTS
					INSERT INTO @RESULTS SELECT NULL, SPERSON_ID, CUSTOMER, PROJECT, PRODUCT, ODM,/* MONTH,*/NULL, QUARTER, YEAR, QUANTITY1,QUANTITY2,QUANTITY3,PRICE1,PRICE2,PRICE3, /*PRICE,*/NULL, NULL, NULL, NULL, CONFIDENCE_LEVEL1,CONFIDENCE_LEVEL2,CONFIDENCE_LEVEL3, CUSTID, PROJID, PRODID, ODMID, FORECAST_TYPE, SPERSON_NAME, QUARTER_START, QUARTER_END FROM @TOTAL_RESULTS																								
	 		CLOSE SPERSON_CURSOR
	 		DEALLOCATE SPERSON_CURSOR 
			
		END

 		
	SELECT * FROM @RESULTS	
 
 END
 